package com.qf.dao.impl;

import com.qf.dao.ProductDao;
import com.qf.entity.Product;
import com.qf.utils.DBUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * @title:ProductDaoImpl
 * @Descripe:
 * @author:Wjl
 * @date:2023/4/20 17:13
 */
public class ProductDaoImpl implements ProductDao {

    /**
     *
     * @param ps_id：商品状态
     * @param pt_id：商品类型
     * @param pro_name：商品名
     * @return
     * @throws SQLException
     */
    @Override
    public Integer DataCount(Integer ps_id, Integer pt_id, String pro_name) throws SQLException {
        QueryRunner qr = DBUtils.DB.getQr();
        List<Object> parmList = new ArrayList<>();
        StringBuffer sql = new StringBuffer("select * from product where 1=1 ");

        if (ps_id!=null&&!ps_id.equals("")){
            sql.append(" and ps_id=?");
            parmList.add(ps_id);
            System.out.println("ps_id："+ps_id);
        }
        if (pt_id!=null&&!pt_id.equals("")){
            sql.append(" and pt_id=?");
            parmList.add(pt_id);
            System.out.println("pt_id："+pt_id);
        }
        if (pro_name!=null&&!pro_name.equals("")){
            sql.append(" and pro_name like ?");
            parmList.add("%"+pro_name+"%");
            System.out.println("pro_name："+pro_name);
        }

        Object count;
        if (parmList==null||parmList.size()<=0){
            count = qr.query(sql.toString(), new ScalarHandler());
        }else {
            count = qr.query(sql.toString(), new ScalarHandler(), parmList.toArray());
        }
        return count ==null ? 0 : Integer.parseInt(count+"");
    }


    /**
     *
     * @param ps_id：商品编号
     * @param pt_id：商品类型
     * @param pro_name：商品名
     * @param currentPageNo：第几页
     * @param pageSize：当前页总记录条数
     * @return
     * @throws SQLException
     */
    @Override
    public List<Product> selectAllProduct(Integer ps_id, Integer pt_id, String pro_name, Integer currentPageNo, Integer pageSize) throws SQLException {
        QueryRunner qr = DBUtils.DB.getQr();
        List<Object> parmList = new ArrayList<>();
        StringBuffer sql = new StringBuffer("select * from product where 1=1 ");

        if (ps_id!=null&&!"".equals(ps_id)){
            sql.append(" and ps_id=?");
            parmList.add(ps_id);
        }
        if (pt_id!=null&&"".equals(pt_id)){
            sql.append(" and pt_id=?");
            parmList.add(pt_id);
        }
        if (pro_name!=null&&!"".equals(pro_name)){
            sql.append(" and pro_name like ?");
            parmList.add("%"+pro_name+"%");
        }
        sql.append(" limit ?,?");
        parmList.add((currentPageNo-1)*pageSize);
        parmList.add(pageSize);

        System.out.println(parmList.toString());
        List<Product> query =null;
        if (parmList==null||parmList.size()<=0){
            query = qr.query(sql.toString(), new BeanListHandler<Product>(Product.class));
        }else {
            query = qr.query(sql.toString(),new BeanListHandler<Product>(Product.class),parmList.toArray());
        }
        System.out.println(query.toString());
        return query;

    }

    /**
     * 通过id或者商品名查询商品详细数据
     * @param pro_id
     * @param pro_name
     * @return
     * @throws SQLException
     */
    @Override
    public Product selectById_Or_Name(Integer pro_id, String pro_name) throws SQLException {
        QueryRunner qr = DBUtils.DB.getQr();
        List<Object> parmList = new ArrayList<>();
        StringBuffer sql = new StringBuffer("select * from product where 1=1 ");

        if (pro_id!=null&&!"".equals(pro_id)){
            sql.append(" and pro_id=?");
            parmList.add(pro_id);
        }
        if (pro_name!=null&&!"".equals(pro_name)){
            sql.append(" and pro_name=?");
            parmList.add(pro_name);
        }
        Product product =null;
        if (parmList==null||parmList.size()<=0){
            product = qr.query(sql.toString(), new BeanHandler<Product>(Product.class));
        }else {
            product = qr.query(sql.toString(),new BeanHandler<Product>(Product.class),parmList.toArray());
        }
        return product;
    }

    /**
     * 添加商品
     * @param pro_name：商品名
     * @param pro_photo：商品图片地址
     * @param ps_id：商品状态id
     * @param pro_purchasing_price：商品进货价
     * @param pro_sale_price：商品售价
     * @param pro_number：商品库存
     * @param pt_id：商品类型
     * @param pro_create：商品创建日期
     * @return
     */
    @Override
    public int addProduct(String pro_name, String pro_photo, Integer ps_id, Double pro_purchasing_price, Double pro_sale_price, Integer pro_number, Integer pt_id, Date pro_create) throws SQLException {
        QueryRunner qr = DBUtils.DB.getQr();
        String sql = "insert into product(pro_name,pro_photo,ps_id,pro_purchasing_price,pro_sale_price,pro_number,pt_id, pro_create)" +
                "values(?,?,?,?,?,?,?,?)";
        Object[] objects = {pro_name,pro_photo,ps_id,pro_purchasing_price,pro_sale_price,pro_number,pt_id, pro_create};
        int update = qr.update(sql, objects);
        return update;
    }

    /**
     * 根据id删除物品
     * @param pro_id
     * @return
     * @throws SQLException
     */
    @Override
    public int delProduct(int pro_id) throws SQLException {
        QueryRunner qr = DBUtils.DB.getQr();
        String sql = "delete from product where pro_id=?";
        Object[] objects={pro_id};
        int update = qr.update(sql,objects);
        return update;
    }

    /**
     * 根据id修改物品
     * @param product
     * @return
     */
    @Override
    public int updateProduct(Product product) throws SQLException {
        QueryRunner qr = DBUtils.DB.getQr();

        String sql = "update product set pro_name=?,pro_photo=?,ps_id=?,pro_purchasing_price=?,pro_sale_price=?,pro_number,pt_id=? where pro_id=?)";
        Object[] objects = {product.getPro_name(),product.getPro_photo(),product.getPs_id(),product.getPro_purchasing_price(),product.getPro_sale_price(),
                            product.getPro_number(),product.getPt_id(),product.getPro_id()};

        int update = qr.update(sql, objects);
        return update;
    }
}
